using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public UfVO getUf(int? idUf, string sigla)
		{
			UfVO ufVO = null;
			List<UfVO> listUfVOs = getUfs(idUf, sigla, null);
			if (listUfVOs != null && listUfVOs.Count > 0)
			{
				ufVO = listUfVOs[0];
			}
			return ufVO;
		}

		public UfVO getUf(int? idUf)
		{
			return getUf(idUf, null);
		}

		public List<UfVO> getUfs()
		{
			return getUfs(null, null, null);
		}

		public List<UfVO> getUfs(int? idUf)
		{
			return getUfs(idUf, null, null);
		}

		public List<UfVO> getUfs(int? idUf, string sigla, string uf)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT uf.idUf as uf_idUf, uf.sigla as uf_sigla, uf.uf as uf_uf, uf.Cadastro as uf_Cadastro, uf.CadastradoPor as uf_CadastradoPor, uf.Edicao as uf_Edicao, uf.EditadoPor as uf_EditadoPor  ");
			query.AppendLine("FROM uf as uf ");
			if (idUf != null)
			{
				query.AppendLine(where + "uf.idUf=@idUf ");
				where = "AND ";
			}
			if (sigla != null)
			{
				query.AppendLine(where + "uf.sigla LIKE @sigla ");
				where = "AND ";
			}
			if (uf != null)
			{
				query.AppendLine(where + "uf.uf=@uf ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY uf.sigla ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idUf!= null) cmd.Parameters.Add(getParameter(cmd, "@idUf", idUf));
			if (sigla!= null) cmd.Parameters.Add(getParameter(cmd, "@sigla", "%"+sigla+"%"));
			if (uf!= null) cmd.Parameters.Add(getParameter(cmd, "@uf", uf));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<UfVO> listUf = new List<UfVO>();

				while (reader.Read())
				{
					UfVO ufVO = UfVO.CreateVO();
					ufVO.idUf = reader.GetInt32(reader.GetOrdinal("uf_idUf"));
					ufVO.sigla = reader.GetString(reader.GetOrdinal("uf_sigla"));
					ufVO.uf = reader.GetString(reader.GetOrdinal("uf_uf"));
					ufVO.cadastro = reader.GetDateTime(reader.GetOrdinal("uf_Cadastro"));
					ufVO.cadastradoPor = reader.GetString(reader.GetOrdinal("uf_CadastradoPor"));
					ufVO.edicao = reader.GetDateTime(reader.GetOrdinal("uf_Edicao"));
					ufVO.editadoPor = reader.GetString(reader.GetOrdinal("uf_EditadoPor"));


					listUf.Add(ufVO);
				}
				reader.Close();
				return listUf;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setUf(UfVO ufVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE uf ");
			query.AppendLine("SET sigla=@sigla, uf=@uf,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idUf=@idUf ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idUf", ufVO.idUf));
			cmd.Parameters.Add(getParameter(cmd, "@sigla", ufVO.sigla));
			cmd.Parameters.Add(getParameter(cmd, "@uf", ufVO.uf));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", ufVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", ufVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", ufVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", ufVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newUf(UfVO ufVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO uf ");
			query.AppendLine("  ( sigla, uf, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @sigla, @uf, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@sigla", ufVO.sigla));
			cmd.Parameters.Add(getParameter(cmd, "@uf", ufVO.uf));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", ufVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", ufVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", ufVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", ufVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('uf') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delUf(UfVO ufVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM uf ");
			query.AppendLine("WHERE idUf=@idUf ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idUf", ufVO.idUf));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
	}
}
